const router = require("koa-router")();
const { sqlquery } = require("../APP/LK.js");
const axios = require("axios");
//文件上传后台开始
const multer = require("koa-multer");
//配置
var storage = multer.diskStorage({
  //文件保存路径
  destination: function (req, file, cb) {
    cb(null, "public/uploads/"); //注意路径必须存在
  },
  //修改文件名称
  filename: function (req, file, cb) {
    var fileFormat = file.originalname.split(".");
    cb(null, Date.now() + "." + fileFormat[fileFormat.length - 1]);
  },
});
//加载配置
var upload = multer({ storage: storage });

router.post("/doAdd", upload.single("file"), async (ctx, next) => {
  let query = ctx.query;
  console.log(query);
  let path = ctx.req.file.filename;
  path = path.replace(/\\/g, "/");
  let type = query.type;
  let openid = query.openid;
  let sql = `insert into house_img (indoor,type,openid) values ('${path}','${type}','${openid}')`;
  console.log(sql);
  await sqlquery(sql);
  ctx.body = {
    file: ctx.req.file.file, //返回文件名
    body: ctx.req.body,
  };
});
//文件上传后台结束

//客户端获取2热门楼盘和1二手房  --已写
router.get("/getHot", async (ctx, next) => {
  let state = ctx.query.state;
  let sql = `SELECT *, houses.id as hid,houses.type as htp,house_img.type as itp,house_img.id as iid FROM houses inner JOIN house_img on houses.id=house_img.house_id where house_img.type=4 and state="${state}"`;
  let res = await sqlquery(sql);
  let json = {
    code: 200,
    msg: "获取成功",
    data: res,
  };
  ctx.body = json;
});

//客户端获取楼盘
router.get("/getRoom", async (ctx, next) => {
  let query = ctx.query;
  console.log(78787, query);
  let sql = `SELECT *, houses.id as hid,houses.type as htp,house_img.type as itp,house_img.id as iid FROM houses inner JOIN house_img on houses.id=house_img.house_id where sell='${query.state}' and house_img.type=4`;
  if (query.districe != "undefined" && query.districe != "") {
    sql += ` and district='${query.districe}'`;
  } else if (query.type != "undefined" && query.type != "") {
    sql += ` and state='${query.type}'`;
  } else if (query.deploy != "undefined" && query.deploy != "") {
    sql += ` and deploy like '%${query.deploy}%'`;
  }
  console.log(sql,121212);
  let res = await sqlquery(sql);
  let json = {
    code: 200,
    msg: "获取成功",
    data: res,
  };
  ctx.body = json;
});

//客户端获取首页轮播图
router.get("/getBanner", async (ctx, next) => {
  let res = [
    { img: "http://127.0.0.1:3000/images/banner/banner1.jpg" },
    { img: "http://127.0.0.1:3000/images/banner/banner2.jpg" },
    { img: "http://127.0.0.1:3000/images/banner/banner3.jpg" },
  ];
  let json = {
    code: 200,
    msg: "获取成功",
    data: res,
  };
  ctx.body = json;
});

// 获取首页经纪人  --已写
router.get("/getBroker", async (ctx, next) => {
  let sql = `select broker.*,company.* from broker inner join company on broker.company=company.id`;
  let res = await sqlquery(sql);
  let json = {
    code: 200,
    msg: "获取成功",
    data: res,
  };
  ctx.body = json;
});

//获取小区  --已写
router.get("/getPlot", async (ctx, next) => {
  let query = ctx.query;
  console.log(query.value);
  let sql = "select * from plot";
  if (!query.value) {
    sql = sql;
  } else {
    sql += ` where plot_site like '%${query.value}%'`;
  }
  let res = await sqlquery(sql);
  let json = {
    code: 200,
    msg: "获取成功",
    data: res,
  };
  ctx.body = json;
});


//客户端获取房源及经纬度  
router.get("/getHouseSide", async (ctx, next) => {
  let sql = `select * from houses where latitude!='' and longitude!=''`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});


//获取区域如海淀怀柔等
router.get("/getDistrict", async (ctx, next) => {
  let sql = `select * from district`;
  let res = await sqlquery(sql);
  let json = {
    code: 200,
    msg: "获取成功",
    data: res,
  };
  ctx.body = json;
});

//获取小区
router.get("/getPlot", async (ctx, next) => {
  let query = ctx.query;
  console.log(query.value);
  let sql = "select * from plot";
  if (!query.value) {
    sql = sql;
  } else {
    sql += ` where plot_site like '%${query.value}%'`;
  }
  let res = await sqlquery(sql);
  let json = {
    code: 200,
    msg: "获取成功",
    data: res,
  };
  ctx.body = json;
});

//获取求租求购
router.get("/getRent", async (ctx, next) => {
  let query = ctx.query;
  console.log(8888,query);
  let sql = `select * from seek where state='${query.state}'`;
  if(query.type){
    sql+=` and type='${query.type}'`
  }else if(query.area){
    sql+=` and district='${query.area}'`
  }
  console.log(sql);
  let res = await sqlquery(sql);
  let json = {
    code: 200,
    mas: "获取成功",
    data: res,
  };
  ctx.body = json;
});

//添加房屋
router.post("/addRoom", async (ctx, next) => {
  let query = ctx.request.body;
  console.log(1212, query);
  // 点击发布首先执行sql将传递参数插入数据库
  let sql = `insert into houses (plot_id,type,district,deploy,floor,floor_sum,area,title,describes,phone,price,state,sell) values ('${query.plot}','${query.type}','${query.district}','${query.deploy}','${query.floor}','${query.floor_sum}','${query.area}','${query.title}','${query.describe}','${query.phone}','${query.price}','${query.state}','${query.sell}')`;
  await sqlquery(sql);
  //查找房源表通过id排序倒序第一个来找到最后一条数据
  let sqll = `select * from houses order by id desc limit 1`;
  let resss = await sqlquery(sqll);
  // 通过最后一条数据的id来修改房源图片表中与房源表关联的数据信息，判断openid等于传递的openid和house_id是空满足这两个条件执行
  let sql2 = `update house_img set house_id='${resss[0].id}' where openid='${query.openid}' and house_id is null`;
  console.log(sql2);
  await sqlquery(sql2);
  let json = {
    code: 200,
    msg: "添加成功",
  };
  ctx.body = json;
});

//添加金额
router.post("/addMoney", async (ctx, next) => {
  let query = ctx.request.body;
  console.log(query);
  let sql = `insert into payment (price) values ('${query.money}')`;
  await sqlquery(sql);
  let json = {
    code: 200,
    msg: "添加成功",
  };
  ctx.body = json;
});

//求租或者求购接口
router.post("/addSeek", async (ctx, next) => {
  let query = ctx.request.body;
  console.log(query);
  let sql = `insert into seek (plot_id,type,district,area,price,title,describes,phone,state,money,openid) values ('${query.plot}','${query.type}','${query.district}','${query.area}','${query.price}','${query.title}','${query.describe}','${query.phone}','${query.state}','${query.money}','${query.openid}')`;
  console.log(sql);
  await sqlquery(sql);
  let json = {
    code: 200,
    msg: "添加成功",
  };
  ctx.body = json;
});

//客户端登陆授权接口  --已写
router.get("/getOpenid", async (ctx, next) => {
  let query = ctx.query;
  let user_phone = "13739660787";
  let appid = "wx7fba732207d8ba53";
  let secret = "5deb3802e2ca80608ea030e794b300a0";
  let url =
    "https://api.weixin.qq.com/sns/jscode2session?appid=" +
    appid +
    "&secret=" +
    secret +
    "&js_code=" +
    query.code +
    "&grant_type=authorization_code";
  let setAxios = await axios.get(url);
  console.log("dsffda", setAxios.data);
  let sql1 = `select * from clientlogin where openid='${setAxios.data.openid}'`;
  let res = await sqlquery(sql1);
  if (res.length > 0) {
    json = { code: "0000", msg: "登录成功", data: res };
  } else {
    let sql = `insert into clientlogin (openid,user_avatarUrl,user_city,user_country,user_gender,user_language,user_nickName,user_province,user_phone) values('${setAxios.data.openid}','${query.user_avatarUrl}','${query.user_city}','${query.user_country}','${query.user_gender}','${query.user_language}','${query.user_nickName}','${query.user_province}','${user_phone}')`;
    await sqlquery(sql);
    let sql1 = `select * from clientlogin where openid='${setAxios.data.openid}'`;
    let res = await sqlquery(sql1);
    json = { code: "0000", msg: "登录成功", data: res };
  }
  ctx.body = json;
});

//客户端获取我的发布列表  --已写
router.get("/getHouse", async (ctx, next) => {
  let jg = ctx.query.jg;
  let sj = ctx.query.sj;
  let sql = `select *,houses.type as htp from houses inner join house_img on houses.id=house_img.id and state='${jg}' and house_state='${sj}'`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});

//客户端获取资讯列表  --已写
router.get("/getInformation", async (ctx, next) => {
  let sql = `select * from information`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});
//客户端获取收藏列表  --已写
router.get("/getCollect", async (ctx, next) => {
  let sql = `select * from collect`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});

//客户端获取房源详情  --已写
router.get("/getHousing", async (ctx, next) => {
  let id = ctx.query.id;
  let sql = `select * from houses where id='${id}'`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});

//客户端获取推荐房源  --已写
router.get("/getHouses", async (ctx, next) => {
  let sql = `select * from houses`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});

//客户端获取房源详情图片 --已写
router.get("/getHouseImage", async (ctx, next) => {
  let id = ctx.query.id;
  let sql = `select * from house_img where house_id='${id}'`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});

//客户端添加收藏列表  --已写
router.post("/addCollect", async (ctx, next) => {
  let ids = ctx.request.body.id;
  let img = ctx.request.body.img;
  let title = ctx.request.body.title;
  let collect_desc = ctx.request.body.desc;
  let price = ctx.request.body.price;
  let category = ctx.request.body.category;
  let sql = `insert into collect (img,title,collect_desc,price,category,house_id) values('${img}','${title}','${collect_desc}','${price}','${category}','${ids}')`;
  await sqlquery(sql);
  ctx.body = { code: "0000", msg: "添加成功" };
});

//客户端获取系统消息  --已写
router.get("/getSystem", async (ctx, next) => {
  let sql = `select * from systems`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});

//客户端添加反馈内容  --已写
router.post("/addProblem", async (ctx, next) => {
  let text = ctx.request.body.text;
  let phone = ctx.request.body.phone;
  let reply = ctx.request.body.reply;
  let sql = `insert into problem (problem_phone,problem_text,problem_reply) values('${phone}','${text}','${reply}')`;
  await sqlquery(sql);
  ctx.body = { code: "0000", msg: "添加成功" };
});

//客户端获取支付表列表  --已写
router.get("/getPayMent", async (ctx, next) => {
  let jg = ctx.query.jg;
  let sql = `select * from payment where mouth='${jg}'`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});

//客户端获取团购买房  --已写
router.get("/getGroup", async (ctx, next) => {
  let sql = `select * from group_purchase as gp left join apply as a on gp.id=a.group_id`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});


//客户端团购买房报名  --已写
router.post("/addApply", async (ctx, next) => {
  let query=ctx.request.body;
  console.log(8745,query);
  let sql = `insert into apply (group_id,user) values ('${query.id}','${query.openid}')`;
   await sqlquery(sql);
  ctx.body = { code: "200", msg: "已报名"};
});

//获取经纪人数量
router.get("/getBrokerNumber", async (ctx, next) => {
  let sql = `select count(1) as number from broker`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
})

//客户端查询楼市圈数据
router.get('/getLouSHI', async function (ctx, next) {
  let sql = `SELECT a.id,a.yue,a.yueri,a.content,a.broker_id,c.name,c.img headimg,b.img FROM building_circle a left JOIN broker c on c.id=a.broker_id left JOIN building_circle_img b on a.id = b.key_id ORDER BY a.id desc`;
  let res = await sqlquery(sql);
  var map = {}
  res.forEach(function (item) {
    console.log(item.id)
    map[item.id] = item
  })
  res.forEach(function (item) {
    if (map[item.id] && item.img) {
      (map[item.id].children || (map[item.id].children = [])).push(item.img)
    }
  });
  console.log(map)
  ctx.body = {
    code: '200',
    msg: '楼市圈列表查询成功',
    data: map
  }
})

//客户端获取房源及经纬度  
router.get("/getHouseSide", async (ctx, next) => {
  let qy = ctx.query.qy;
  let jg = ctx.query.jg;
  let hx = ctx.query.hx;
  let jl = ctx.query.jl;
  let sql = `select * from houses where latitude!='' and longitude!='' and district='${qy}' and price='${jg}' and deploy='${hx}' and area='${jl}'`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});

//客户端获取经纪人前三名
router.get("/getAgent", async (ctx, next) => {
  let sql = `select * from broker inner join company on broker.company=company.id`;
  let res = await sqlquery(sql);
  ctx.body = { code: "0000", msg: "查询成功", data: res };
});

//客户端获取房源详情取消收藏
router.post("/delCollect", async (ctx, next) => {
  let id = ctx.request.body.id;
  let sql = `delete from collect where house_id='${id}'`;
  await sqlquery(sql);
  ctx.body = { code: "0000", msg: "取消收藏成功"};
});


module.exports = router;
